<?php
/*
 * desinger by caoch 2014-6-20 修改
 * To change this template file, choose Tools | Templates
 * and open the template in the editor. * 
 */

namespace Common\Controller;
/**
 * Description of AnalyzeDataController
 *
 * @author caoch 2014-6-29
 */
class AnalyzeDataController extends TransParamController {
    
    /**
     * 方法：根据提交的参数返回最终结果
     * 参数：分析类型，各个维度值
     * 返回：结果数组
     */
    public function getAnalyzeDataResults(){
		
        if(empty($this->error)){
            switch ($this->type){
                case 1://排行榜基础数据展示
                    if(!empty($this->ranking)&&$this->yNums>0&&$this->vNums>0){
                        $resultArr = $this->getToppicData();
                    }else{
                        $this->error = array('R1'=>'缺少排行榜分析必要参数');
                    }
                    break; 
                case 2://同比及环比基础表展示
                    if((!empty($this->count['YoY'])||!empty($this->count['MoM']))&&$this->vAxis>0){
                        $resultArr = $this->getRatioData();
                    }else{
                        $this->error = array('R2'=>'缺少同比及环比必要参数');
                    }
                    break;
                case 3://带供应量的基础表展示
                    if($this->yAxis&&$this->vAxis>0){
                        $resultArr = $this->getStockData();
                    }else{
                        $this->error = array('R3'=>'缺少供应数据分析必要参数');
                    }
                    break;
                case 9://挖掘
                    if(!empty($this->ranking)&&$this->yNums>0&&$this->vNums>0){
                        $resultArr = $this->getDigData();
                    }else{
                        $this->error = array('R3'=>'缺少供应数据分析必要参数');
                    }
                    break;
                case 0://交叉分析基础表展示
                default :
                    if($this->xNums + $this->yNums + $this->vNums > 0){
                        $resultArr = $this->getAnalyzeData();
                    }else{
                        $this->error = array('R0'=>'缺少透视分析的必要参数');
                    }
                    break;
            }
            return $resultArr;
        }else{
            return $this->debug(TRUE);
        }
    }
    /**
     * 方法：获取排名分析数据
     * 说明：分各种情况
     */
    public function getToppicData(){
        $h = $this->setHeaderTableFormat($this->getHeadData());
        $r = $this->setRowsDataFormat($this->getTopData());
        
        if($this->count['isYSum'] == 1){
            $f = $this->setFootTableFormat();
        }
        
        $c = $this->setChartFormat();
        
        $d = $this->setDigDataFormat();

        $this->doDropTempTable();
        
        return $this->setReaultOutFormat($h,$r,$f,$c,$d);
    }
    
     /**
     * 方法：获取交叉分析数据
     * 说明：分各种情况
     */
    public function getAnalyzeData(){
        $h = $this->setHeaderTableFormat($this->getHeadData());
        $r = $this->setRowsDataFormat($this->getPivotData());
        
        if($this->count['isYSum'] == 1){
            $f = $this->setFootTableFormat();
        }
        $c = $this->setChartFormat();
        
        $d = $this->setDigDataFormat();
        
        $this->doDropTempTable();
        
        return $this->setReaultOutFormat($h,$r,$f,$c,$d);
    }
    
    public function getStockData(){
        $h = $this->setHeaderTableFormat($this->getStockHeadData());
        $r = $this->setRowsDataFormat($this->setStockDataSql());
        
        if($this->count['isYSum'] == 1){
            //$f = $this->setFootTableFormat();
        }
        $c = $this->setChartFormat();
        
        $d = $this->setDigDataFormat();
        
        $this->doDropTempTable();
        
        return $this->setReaultOutFormat($h,$r,$f,$c,$d);
    }

    /**
     * 方法：挖掘数据
     */
    public function getDigData(){
        $head = $this->setCountHeader();
        
        //结果数组::追加首列
        $t[]='排序';
        $i[]='rank';
        
        //结果数组::追加列标
        foreach ($this->yAxis as $key){
            $t[]=$this->rulesArr[$key]['FieldName'];
            $i[]=$key;
        }
        
        foreach ($head as $key=>$val){
            $t[]=$val;
            $i[]=$key;
        }
        
        $title[]=$t;
        $sql = $this->getTopData();
        $rows = $this->setDigDataRows($sql);

        $result = array_merge($title,$rows);
        
        $this->doDropTempTable();
        
        return $result;
    }

        /**
     * @方法：判断图表样式
     * @参数：维度
     */
    private function setChartType(){
        if($this->type == 1){
            $chart=array('sybar');
            $type=1;
        }else{
            if($this->vNums>1){
                $i=0;
                foreach ($this->vAxis as $val){
                    switch ($val['k']){
                        case 'JMDJ':
                        case 'TNDJ':
                        case 'TS':
                            ++$i;
                            break;
                        case 'JZMJ':
                        case 'TNMJ':
                        case 'CJJE':
                        default :
                            ;
                            break;
                    }
                }
                if($i>0){
                    $chart=array('fixed');
                    $type=1;
                }else{
                    $chart=array('bar','line','xBar','yBar','pie');
                    $type=0;
                }            
            }else{
                $chart=array('bar','line','xBar','yBar','pie');
                $type=0;
            }
        }
        return array(0=>$type,1=>$chart);
    }
    
    
    /**
     * @方法：解析交叉数据分析时的图表维度值
     */
    private function setTopChartAxis(){
        $c = $this->setChartType();
        $chart['t']=$c[1];
        
        $vAxis=array();
        if($this->vNums>0){
            foreach ($this->vAxis as $val){
                if($val['k']==$this->ranking['topColumn'][0]){
                    $uom=$val['u'];
                }
            }
            $vAxis[] = array($this->setColumnName($this->ranking['topColumn'][0]),$this->setCountName($this->ranking['topColumn'][1]),$this->setUomName($uom));
        }
        
        $xAxis[] = array($this->setColumnName($this->ranking['topColumn'][0]),$this->setCountName($this->ranking['topColumn'][1]),$this->setUomName($uom));

        
        $yAxis=array();
        if($this->yNums>0){
            foreach ($this->yAxis as $key){
                $yAxis[] = $this->setColumnName($key);
            }
        }
        
        $chart['x']=$xAxis;
        $chart['y']=$yAxis;
        $chart['v']=$vAxis;
        
        return $chart;
    }
    
    /**
     * @方法：解析交叉数据分析时的图表维度值
     */
    private function setPovitChartAxis(){
        $c = $this->setChartType();
        $chart['t']=$c[1];
        
        $vAxis=array();
        if($this->vNums>0){
            if($chart[0]==1){
                foreach ($this->vAxis as $val){
                    switch ($val['k']){
                        case 'JMDJ':
                        case 'TNDJ':
                        case 'TS':
                            $vAxis[] = array($this->setColumnName($val['k']),$this->setCountName($val['c']),$this->setUomName($val['u']),'r');
                            break;
                        case 'JZMJ':
                        case 'TNMJ':
                        case 'CJJE':
                        default :
                            $vAxis[] = array($this->setColumnName($val['k']),$this->setCountName($val['c']),$this->setUomName($val['u']),'l');
                            break;
                    }                    
                }
            }else{
                foreach ($this->vAxis as $val){
                    $vAxis[] = array($this->setColumnName($val['k']),$this->setCountName($val['c']),$this->setUomName($val['u']),'l');
                }
            }
        }
        
        $xAxis=array();
        if($this->xNums>0){
            foreach ($this->xAxis as $key){
                $xAxis[] = $this->setColumnName($key);
            }
        }else{
            foreach ($this->vAxis as $val){
                $xAxis[] = $this->setColumnName($val['k']);
            }
        }
        
        $yAxis=array();
        if($this->yNums>0){
            foreach ($this->yAxis as $key){
                $yAxis[] = $this->setColumnName($key);
            }
        }else{
            foreach ($this->vAxis as $val){
                $yAxis[] = $this->setColumnName($val['k']);
            }
        }
        
        $chart['xN']=$this->xNums;
        $chart['yN']=$this->yNums;
        $chart['vN']=$this->vNums;
        
        $chart['x']=$xAxis;
        $chart['y']=$yAxis;
        $chart['v']=$vAxis;
        
        return $chart;
    }

    /**
     * @方法：根据参数判断图表类型
     * @参数：
     * 柱状图	bar
        条形图	sybar
        折线图	line
        饼状图	pie
        堆积折线图	sline
        堆积柱状图	xBar
        堆积条形图	yBar
        百分比堆积柱状图	pxBar
        百分比堆积条形图	pyBar
        量价图	fixed
     * @返回：图表类型
     */
    private function setChartFormat(){
        //$cBase=array('bar','sybar','line','pie','sline','xBar','yBar','pxBar','pyBar','fixed');
        if($this->type == 1){
            $chart = $this->setTopChartAxis();
        }else{
            $chart = $this->setPovitChartAxis();
        }
        
        
        return $chart;
    }
    /**
     * 
     * @方法：挖掘数据
     * @return type
     * i为1表示要挖，为0表示不需要挖；
     */
    private function setDigDataFormat(){
        $i=0;
        $config=array();
        if($this->xNums>0){
            foreach ($this->xAxis as $key){
                $key=  strtoupper($key);
                if($this->rulesArr[$key]['FieldType']==2){
                    ++$i;
                }
            }
        }
        
        if($this->yNums>0){
            foreach ($this->yAxis as $key){
                $key=  strtoupper($key);
                if($this->rulesArr[$key]['FieldType']==2){
                    ++$i;
                }
            }
        }
        if($this->DataType=='STORED'){
            $config['i']=0;
        }else{
            $config['i']=1;
        }
        if($i>0){
            $config['l']=1;
        }else{
            $config['l']=0;
        }
        $config['i']=1;
        $config['t']=1;
        
        return $config;
    }

    /**
     * @方法：解析表头数组，将结果解析成表格输出所需要格式
     * @参数：表头数组
     * @返回：表头数组
     */
    private function setHeaderTableFormat($data){
                
        $head['i']=$data;
        $head['f']=$this->getFixedColumn();//fixld简称，左边固定列名
        $head['r']=$this->getMergeHeadColumn();//需要纵向合并的列
        $head['a']=$this->getHeadLevel();//表头维度值，用于挖掘
        if($this->type==1){
            $head['g'] = $this->ranking['topColumn'][0].'_'.$this->ranking['topColumn'][1];//表头排序
        }
        
        return $head;
    }
    
    /**
     * @方法：解析FOOT数组，将结果解析成表格输出所需要格式
     * @参数：表头数组
     * @返回：表头数组
     */
    private function setFootTableFormat(){
        $yAxis = $this->getFixedColumn();//左侧列名数组
        $footData[]=$this->setHeadOutFormat('合计', $yAxis[0], '', count($yAxis));

        foreach ($this->footData as $key=>$val){
            $footData[] = $this->setHeadOutFormat($val, $key, $key, '');
        }
        
        if($this->count['isXSum']==1){//同时存在横向合计时
            //var_dump($this->sumData);
            foreach($this->vAxis as $val){
                if($val['k']!='RATE'){
                    $index = $this->setCountColumnFormats($val['k'], $val['c'], $val['u']);
                    $value = $this->setNumberFormat($key,$this->sumData[0][$index]);
                    $key = 'T_'.$index;
                    
                    $footData[] = $this->setHeadOutFormat($value, $key, $key, '');
                }
            }
        }        
          
        $foot['i'][0]=$footData;
        $foot['f']=$this->getFixedColumn();//fixld简称，左边固定列名
        $foot['h']=$this->getFixedColumn();//需要纵向合并的列
        
        return $foot;
    }
    
    /**
     * @方法：处理数组的统计值：对数组进行求和，平均，最大，最小处理
     * @参数：数组
     * @返回：值
     */
    private function setCountArrayValue($code,$count,$array){
        if(is_array($array)){
            switch ($count){
                case 'SUM':
                    $value = array_sum($array);
                    break;
                case 'AVG':
                    $value = array_sum($array)/count($array);
                    break;
                case 'MIN':
                    $value = min($array);
                    break;
                case 'MAX':
                    $value = max($array);
                    break;
            }
        }else{
            $value = 0;
        }
        $key = $this->setCountCode($code);
        return $this->setNumberFormat($key,$value);
    }

        /**
     * @方法：解析需要计算横向合计时表体数据
     * @参数：数据查询conn
     * @返回：rows数组
     */
    private function getXSumRowsData($yAsix,$stmt){
        $rows = array();        

        if($this->xNums>0 && $this->yNums==0){
            $type=1;//按行设置小数位
        }else{
            $type=0;
        }
        
        if($this->vNums>1){//多个统计值
            while ($result = sqlsrv_fetch_array( $stmt , SQLSRV_FETCH_ASSOC)){
                $lines = array();
                $total = array();

                foreach ($result as $key=>$val){
                    if(in_array($key, $yAsix)){
                        $lines[$key] = $val;
                    }else{
                        if($type==1){
                            $code = $result['id'];
                        }else{
                            $code = $key;
                        }

                        $nums = $this->setNumberFormat($code,$val);
                        
                        //数组下标
                        $k = substr($key,1) % $this->vNums;//余数作为下标
                        $total[$k][]=$nums;
                        
                        $lines[$key] = $nums;
                    }
                }
                
                //追加合计值
                foreach ($total as $in=>$array){
                    $index = 'T_'.$this->vAxis[$in]['k'].'_'.$this->vAxis[$in]['c'];
                    
                    $lines[$index] = $this->setCountArrayValue($this->vAxis[$in]['k'],$this->vAxis[$in]['c'],$array);
                }
                
                $rows[]=$lines;
            }
        }else{//单个统计
            while ($result = sqlsrv_fetch_array( $stmt , SQLSRV_FETCH_ASSOC)){
                $lines = array();
                $total = array();

                foreach ($result as $key=>$val){
                    if(in_array($key, $yAsix)){
                        $lines[$key] = $val;
                    }else{
                        if($type==1){
                            $code = $result['id'];
                        }else{
                            $code = $key;
                        }
                        
                        $nums = $this->setNumberFormat($code,$val);
                        $total[] = $nums;
                        
                        $lines[$key] = $nums;
                    }
                }
                
                $tSum='T_'.$this->vAxis[0]['k'].'_'.$this->vAxis[0]['c'];
                $lines[$tSum] = $this->setCountArrayValue($this->vAxis[0]['k'],$this->vAxis[0]['c'],$total);
                
                $rows[]=$lines;
            }
        }
                
        return $rows;
    }

    /**
     * @方法：根据键值获取KEY
     * @数组
     */
    private function getArrayKeys($array,$value){
        foreach ($this->vAxis as $key=>$val){
            if($value == $val['k'].'_'.$val['c']){
                $keys = $key;
            }
        }
        return $keys;
    }

        /**
     * @方法：解析不需要计算横向合计时表体数据:增加数据格式的处理
     * @参数：数据查询conn
     * @返回：rows数组
     */
    private function getNotXSumRowsData($yAsix,$stmt){
        $rows = array();
        
        if($this->xNums>0 && $this->yNums==0){
            $type=1;//按行设置小数位     
            
        }else{
            $type=0;
        }
            
        
        //处理结果数据
        while ($result = sqlsrv_fetch_array( $stmt , SQLSRV_FETCH_ASSOC)){
            $lines = array();
            foreach ($result as $key=>$val){
                if(in_array($key, $yAsix)){
                    $lines[$key] = $val;
                }else{
                    if($type==1){
                        $code = $result['id'];
                    }else{
                        $code = $key;
                    }
                    $lines[$key] = $this->setNumberFormat($code,$val);
                }                
            }
            if($type==1){
                $index = $this->getArrayKeys($this->vAxis,$result['id']);
                $res[$index]=$lines;
            }else{
                $rows[]=$lines;
            }
        }
        if($type==1){
            for ($j=0;$j<$this->vNums;$j++){
                $rows[$j]=$res[$j];
            }
        }
        
        return $rows;        
    }
    

        /**
     * @方法：处理查询返回的数据行结果，按格式输出
     * @参数：查询数据的sql
     * @返回：数组
     */
    private function setRowsDataFormat($sql){
        //连库
        $db = new SqlsrvController();
        $stmt = $db->query($sql);
        //die( print_r( sqlsrv_errors(), true));
        //处理不含数字的列名
        if($this->yNums){//存在y维度
            $yAsix=$this->yAxis;

            if($this->type == 1){
                $yAsix[] = 'rank';
            }
        }else{
            $yAsix[]='yAxis';
        }
        //追加id值的处理
        $yAsix[]='id';

        //获取表体数据
        if($this->count['isXSum'] == 1){
            $rows['i'] = $this->getXSumRowsData($yAsix,$stmt);
        }else{
            $rows['i'] = $this->getNotXSumRowsData($yAsix,$stmt);
        }

        $rows['f'] = $this->getFixedColumn();//固定列
        $rows['h'] = $this->getColorColumn();//颜色不涉及到的列
        $rows['r'] = $this->getMergeColumn();//颜色不涉及到的列
        $rows['a'] = $this->getRowsLevel();//用于数据挖掘的表示
        
        if($this->type==1){
            $rows['g'] = $this->ranking['topColumn'][0].'_'.$this->ranking['topColumn'][1];//用于颜色标列名
        }        
        
        return $rows;
    }
    
    /**
     * @方法：解析最终结果格式
     * @参数：h,r,f,c等维度参数
     * @返回：格式数据
     */
    private function setReaultOutFormat($h,$r,$f,$c,$d){
        if(!empty($h)){
            $res['d']['h']=$h;
        }
        if(!empty($r)){
            $res['d']['r']=$r;
        }
        if(!empty($f)){
            $res['d']['f']=$f;
        }
        if(!empty($d)){
            $res['d']['c']=$d;
        }
        if(!empty($c)){
            $res['c']=$c;
        }
        return $res;
    }

    private function setDigDataRows($sql){
        $db = new SqlsrvController();
        $stmt = $db->query($sql);    
        
        $rows = array();
        $i=1;
        while ($result = sqlsrv_fetch_array( $stmt , SQLSRV_FETCH_ASSOC)){
            foreach ($result as $key=>$val){
                if($key != 'id'){
                    if(in_array($key, $this->yAxis)){
                        $rows[$i][]=$val;
                    }elseif($key=='rank'){
                        $rows[$i][]=$val;
                    }else{
                        $rows[$i][]=$this->setNumberFormat($key,$val);
                    }                    
                }
            }            
            ++$i;
        }
        return $rows;
    }
}
